<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Admin;
use App\Models\Cities;
use App\Models\FileUpload;
use App\Models\FilmTypeMaster;
use App\Models\MacroManagement;
use App\Models\MakeMaster;
use App\Models\ModelMaster;
use App\Models\PartMaster;
use App\Models\ProvinceMaster;
use App\Models\RegionMaster;
use Encore\Admin\Grid\Model;
use Illuminate\Http\Request;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;

class TablesController extends Controller
{

    public function index(Request $request)
    {
        $user_id = session()->get('admin.id');
        $user_type = session()->get('admin.user_type');
        $chart = [];
        if ($user_type == 1) {
            // $data = $this->gly_index($request);
            $chart = $this->charts_index($request);
            $chart = $chart['charts'];
        } elseif ($user_type == 2) {
            // $data = $this->sd_index($request);
            $chart = [
                'c1' => $this->chartA1($request),
            ];
        } else if ($user_type > 3) {
            $role = Roles::find($user_type);
            $permission = [];
            $permission = $role->permissions()->first();
            if (empty($role->id) || empty($permission->id)) {
                session()->forget('admin');
                return redirect('admin/login');
            }
            return redirect('admin/'.$permission->url);
        } else {
            return abort(404);
        }
        $page = [
            'page_name' => 'tables',
            'title' => ts('SJTJ')
        ];

        return view('admin.tables.index', [
            // 'data'=>$data,
            'page' => $page,
            'charts' => $chart,
        ]);
    }

    protected function sd_index(Request $request)
    {
        $user_id = session('admin.id');
        $jxs = DB::table('users')->where('status', 1)
            ->where('creator_id', $user_id)
            ->whereNull('deleted_at')
            ->where('user_type', 3)
            ->count();
        $kh = DB::table('warranty')

            ->whereNull('deleted_at')
            ->where('approved', 1)
            ->whereIn('user_id', function ($query) use ($user_id) {
                $query->select('id')->from('users')
                    ->where('id', $user_id)
                    ->orWhere('creator_id', $user_id);
            })
            ->count();
        $dh = DB::table('roll_transfer')
            ->where('approved', 1)
            ->whereNull('deleted_at')
            ->count();
        $zb = DB::table('precut')
            ->whereNull('deleted_at')
            ->where('status', 0)
            ->whereIn('user_id', function ($query) use ($user_id) {
                $query->select('id')->from('users')
                    ->where('id', $user_id)
                    ->orWhere('creator_id', $user_id);
            })
            ->count();

        $panel = [
            [
                'title' => ts('dealer'),
                'no' => $jxs,
                'icon' => 'fa-user',
                'url' => '/admin/dealer?status=1',
            ],
            [
                'title' => ts('AZSL'),
                'no' => $kh,
                'icon' => 'fa-wrench',
                'url' => '/admin/warranty?status=1',
            ],
            [
                'title' => ts('DANTC'),
                'no' => $zb,
                'icon' => 'fa-cogs',
                'url' => '/admin/precut_deal',
            ],
            [
            ],
        ];
        return [
            'panel' => $panel
        ];

    }
    protected function gly_index(Request $request)
    {

        $sd = DB::table('users')
            ->where('status', 1)
            ->whereNull('deleted_at')
            ->where('user_type', 2)
            ->count();
        $jxs = DB::table('users')
            ->where('status', 1)
            ->whereNull('deleted_at')
            ->where('user_type', 3)
            ->count();
        $kh = DB::table('warranty')
            ->whereNull('deleted_at')
            ->where('approved', 1)
            ->count();
        $dh = DB::table('roll_transfer')
            ->whereNull('deleted_at')
            ->where('approved', 0)
            ->count();

        // $wdea = DB::select("select count(w.id), u.creator_id as user_id from warranty as w left join users as u on u.id=w.user_id WHERE u.user_type=3 group by u.creator_id");
        // $wdis = DB::select("select count(w.id), user_id from warranty as w left join users as u on u.id=w.user_id WHERE u.user_type!=3 group by u.id");

        $panel = [
            [
                'title' => ts('distributor'),
                'no' => $sd,
                'icon' => 'fa-user',
                'url' => '/admin/distributor?status=1',
            ],
            [
                'title' => ts('dealer'),
                'no' => $jxs,
                'icon' => 'fa-user',
                'url' => '/admin/dealer?status=1',
            ],
            [
                'title' => ts('AZSL'),
                'no' => $kh,
                'icon' => 'fa-wrench',
                'url' => '/admin/warranty?status=1',
            ],
            [
                'title' => ts('DCLDH'),
                'no' => $dh,
                'icon' => 'fa-cogs',
                'url' => '/admin/verify/transfer?status=0',
            ],
        ];
        return [
            'panel' => $panel,
        ];
    }

    /*
     * 安装数量，按地区划分*/
    protected function chartA1(Request $request)
    {
        $user_id = session()->get('admin.id');
        $user_type = session()->get('admin.user_type');
        $year = $request->input('year');
        $year = $year ?: date('Y');
        $r = DB::table('region_master')->orderBy('id')->get('id');
        $region = Arr::pluck($r, 'id'); // 地区
        $tm = []; // 补零后的1-12
        foreach (range(1,12) as $v) {
            $k = str_pad($v, 2, 0, STR_PAD_LEFT);
            $tm[$k] = 0;
        }
        $c1 = []; // 安装数量，按地区划分
        if ($user_type == 1) {
            $data = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q, region_id FROM warranty WHERE installation_date like '".$year."%' GROUP BY region_id,oaWonVEI");
        } elseif ($user_type == 2) {
            $data = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q, region_id FROM warranty WHERE installation_date like '".$year."%' and user_id in (select id from users where id='".$user_id."' or creator_id='".$user_id."') GROUP BY region_id,oaWonVEI");
        }
        foreach ($region as $k => $v) {
            $c1[$v] = $tm; // 地区
        }
        foreach ($data as $k => $v) {
            if (empty($c1[$v->region_id])) {
                $c1[$v->region_id] = [];
            }
            $c1[$v->region_id][$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_1 = [
            'name' => ts('region_master.1'),
            'type' => 'line',
            'color' => '#1891ff',
            'data' => array_values($c1[1]),
        ];
        $c1_2 = [
            'name' => ts('region_master.2'),
            'type' => 'line',
            'color' => '#f9cc14',
            'data' => array_values($c1[2]),
        ];
        $c1_3 = [
            'name' => ts('region_master.3'),
            'type' => 'line',
            'color' => '#13c2c2',
            'data' => array_values($c1[3]),
        ];
        $c1_4 = [
            'name' => ts('region_master.4'),
            'type' => 'line',
            'color' => '#2fc25b',
            'data' => array_values($c1[4]),
        ];
        $c1_xAxis = $this->getMon1();
        $c1_legend = [ts('region_master.1'),ts('region_master.2'),ts('region_master.3'),ts('region_master.4')];
        return [
            'series' => [
                $c1_1,
                $c1_2,
                $c1_3,
                $c1_4,
            ],
            'title' => ts('AZSL'),
            'legend' => $c1_legend,
            'xAxis' => $c1_xAxis,
        ];
    }
    protected function chartA2(Request $request)
    {


    }


    public function charts_index(Request $request)
    {
        $admin_id = session('admin.id');
        $user_type = session('admin.user_type');
        if ($user_type == 1) {
            $admin_id = 1;
        }
        $page = [
            'page_name' => 'charts',
            'title' => ts('bbzx'),
        ];
        $year = $request->input('year');
        $year = $year ?: date('Y');
        $r = DB::table('region_master')->orderBy('id')->get('id');
        $region = Arr::pluck($r, 'id'); // 地区
        $tm = []; // 补零后的1-12
        foreach (range(1,12) as $v) {
            $k = str_pad($v, 2, 0, STR_PAD_LEFT);
            $tm[$k] = 0;
        }
        $c1 = []; // 安装数量，按地区划分

        $data = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q, region_id FROM warranty WHERE installation_date like '".$year."%' GROUP BY region_id,oaWonVEI");

        foreach ($region as $k => $v) {
            $c1[$v] = $tm; // 地区
        }
        foreach ($data as $k => $v) {
            if (empty($c1[$v->region_id])) {
                $c1[$v->region_id] = [];
            }
            $c1[$v->region_id][$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_1 = [
            'name' => ts('region_master.1'),
            'type' => 'line',
            'color' => '#1891ff',
            'data' => array_values($c1[1]),
        ];
        $c1_2 = [
            'name' => ts('region_master.2'),
            'type' => 'line',
            'color' => '#f9cc14',
            'data' => array_values($c1[2]),
        ];
        $c1_3 = [
            'name' => ts('region_master.3'),
            'type' => 'line',
            'color' => '#13c2c2',
            'data' => array_values($c1[3]),
        ];
        $c1_4 = [
            'name' => ts('region_master.4'),
            'type' => 'line',
            'color' => '#2fc25b',
            'data' => array_values($c1[4]),
        ];
        $c1_xAxis = [];
        foreach (range(1,12) as $v) {
            $c1_xAxis[] = ts('MON'.$v);
        }
        $c1_legend = [ts('region_master.1'),ts('region_master.2'),ts('region_master.3'),ts('region_master.4')];
        $c5d = [];
        foreach ($c1 as $k => $v) {
            $c5d[] = [
                'name' => ts('region_master.'.$k),
                'value' => array_sum(array_values($v)),
            ];
        }
        $c5 = [
            'series' => $c5d,
            'title' => ts('XSQYFB'),
            'legend' => $c1_legend,
        ];

        $c3 = $this->chart3();

        $c4 = $this->chart401($c3);

        $c2 = $this->chart2();
        return view('admin.admin.charts-index', [
            'page' => $page,
            'charts' => [
                'c1' => [
                    'series' => [
                        $c1_1,
                        $c1_2,
                        $c1_3,
                        $c1_4,
                    ],
                    'title' => ts('AZSL'),
                    'legend' => $c1_legend,
                    'xAxis' => $c1_xAxis,
                ],
                'c2' => $this->chart4($c2),
                'c3' => $c3,
                'c4' => $c4,
                'c5' => $c5,
                'c6' => $this->chart6(),
            ]
        ]);
    }
    protected function chart2()
    {
        $year = request()->input('year') ?: date('Y');
        $d1 = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q FROM warranty WHERE installation_date like '".$year."%' and user_id in (select id from users where user_type=2) GROUP BY oaWonVEI");
        $d2 = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q FROM warranty WHERE installation_date like '".$year."%' and user_id in (select id from users where user_type=3) GROUP BY oaWonVEI");
        $c1 = $c2 = $this->getMon2();
        foreach ($d1 as $k => $v) {
            if (!isset($c1[$v->oaWonVEI])) {
                continue;
            }
            $c1[$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_1 = [
            'name' => ts('distributor'),
            'type' => 'bar',
            //  'barWidth' => '20%',
            'color' => '#1891ff',
            'data' => array_values($c1),
        ];
        foreach ($d2 as $k => $v) {
            if (!isset($c2[$v->oaWonVEI])) {
                continue;
            }
            $c2[$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_2 = [
            'name' => ts('dealer'),
            'type' => 'bar',
            // 'barWidth' => '20%',
            'color' => '#2fc25b',
            'data' => array_values($c2),
        ];

        $data = [
            'series' => [
                $c1_1,
                $c1_2
            ],
            'title' => ts('MXSTJ'),
            'legend' => [ts('distributor'), ts('dealer')],
            'xAxis' => $this->getMon1(),
        ];

        return $data;
    }
    protected function chart401($data)
    {
        // $series = $data['series'];
        $c6d = $data['series'];
        $c4s = [];
        foreach ($c6d as $k => $v) {
            $v['type'] = 'line';
            $v['stack'] = 'total';
            $v['areaStyle'] = [
                'color' => $v['color']
            ];
            unset($v['color']);
            $c4s[] = $v;
        }
        $c4 = [
            'series' => $c4s,
            'title' => ts('SDJXSDB'),
            'legend' => [ts('distributor'), ts('dealer')],
            'xAxis' => $this->getMon1(),
        ];
        return $c4;
    }
    protected function chart3()
    {
        $year = request()->input('year') ?: date('Y');
        $d1 = DB::select("select date_format(installation_date,'%m') AS oaWonVEI, sum(length) as tX1xYm5Q from warranty as w left join warranty_to_vehicle_coverage as wvc on w.id = wvc.warranty_id WHERE wvc.deleted_at is null and installation_date like '".$year."%' AND w.user_id in (SELECT id FROM users WHERE user_type=2) group by oaWonVEI");
        $d2 = DB::select("select date_format(installation_date,'%m') AS oaWonVEI, sum(length) as tX1xYm5Q from warranty as w left join warranty_to_vehicle_coverage as wvc on w.id = wvc.warranty_id WHERE wvc.deleted_at is null and installation_date like '".$year."%' AND w.user_id in (SELECT id FROM users WHERE user_type=3) group by oaWonVEI");

        $c1 = $c2 = $this->getMon2();
        foreach ($d1 as $k => $v) {
            if (!isset($c1[$v->oaWonVEI])) {
                continue;
            }
            $c1[$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_1 = [
            'name' => ts('distributor'),
            'type' => 'bar',
            //  'barWidth' => '20%',
            'color' => '#1891ff',
            'data' => array_values($c1),
        ];
        foreach ($d2 as $k => $v) {
            if (!isset($c2[$v->oaWonVEI])) {
                continue;
            }
            $c2[$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_2 = [
            'name' => ts('dealer'),
            'type' => 'bar',
            // 'barWidth' => '20%',
            'color' => '#2fc25b',
            'data' => array_values($c2),
        ];

        $data = [
            'series' => [
                $c1_1,
                $c1_2
            ],
            'title' => ts('MXSTJ'),
            'legend' => [ts('distributor'), ts('dealer')],
            'xAxis' => $this->getMon1(),
        ];

        return $data;
    }

    protected function chart3Old()
    {
        $year = request()->input('year') ?: date('Y');
        $d1 = $d2 = [];
        $legend = $this->getMon2();
        foreach ($legend as $k => $k) {
            $d1[$k] = DB::table('warranty_to_vehicle_coverage')
                ->whereNull('deleted_at')
                ->select(DB::raw('sum(length) as oaWonVEI'), DB::raw($k . ' as tX1xYm5Q'))
                ->whereIn('warranty_id', function ($query) use ($k, $year) {
                    $query->select('id')->from('warranty')
                        ->where(DB::raw("date_format(installation_date,'%m')"), $k)
                        ->where('installation_date', 'like', $year.'%')
                        ->whereIn('user_id', function ($sq) {
                            $sq->select('id')->from('users')
                                ->where('user_type', 2);
                        });
                });
            // ("select sum(length) as oaWonVEI from warranty_to_vehicle_coverage where warranty_id in (select id from warranty where date_format(installation_date,'%m') ='".$v."' and installation_date like '".$year."%' and user_id in (select id from users where user_type=2))");

            // $d1[$v] = DB::select("select sum(length) as oaWonVEI from warranty_to_vehicle_coverage where warranty_id in (select id from warranty where date_format(installation_date,'%m') ='".$v."' and installation_date like '".$year."%' and user_id in (select id from users where user_type=2))");
            // $d2[$v] = DB::select("select sum(length) as oaWonVEI from warranty_to_vehicle_coverage where warranty_id in (select id from warranty where date_format(installation_date,'%m') ='".$v."' and installation_date like '".$year."%' and user_id in (select id from users where user_type=2))");
        }
        $dg1 = '';
        foreach ($d1 as $k => $v) {
            if (empty($dg1)) {
                $dg1 = $v;
            } else {
                $dg1 = $dg1->union($v);
            }
        }
        // $dg1 = $d1['01']->unionAll(implode(',', $d1));

        return [$dg1->get()];
        /*$d1 = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q FROM warranty WHERE installation_date like '".$year."%' and user_id in (select id from users where user_type=2) GROUP BY oaWonVEI");*/
        /*$d2 = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q FROM warranty WHERE installation_date like '".$year."%' and user_id in (select id from users where user_type=3) GROUP BY oaWonVEI");*/


        return $d1;
    }

    protected function chart4($data)
    {
        $c6d = $data['series'];
        $c4s = [];
        foreach ($c6d as $k => $v) {
            $v['type'] = 'line';
            $v['stack'] = 'total';
            $v['areaStyle'] = [
                'color' => $v['color']
            ];
            unset($v['color']);
            $c4s[] = $v;
        }
        $c4 = [
            'series' => $c4s,
            'title' => ts('SDJXSDB'),
            'legend' => [ts('distributor'), ts('dealer')],
            'xAxis' => $this->getMon1(),
        ];
        return $c4;
    }

    protected function chart6()
    {
        $year = request()->input('year') ?: date('Y');
        $d1 = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q FROM warranty WHERE installation_date like '".$year."%' GROUP BY oaWonVEI");
        $d2 = DB::select("SELECT date_format(installation_date,'%m') AS oaWonVEI,COUNT(id) AS tX1xYm5Q FROM warranty WHERE installation_date like '".($year-1)."%' GROUP BY oaWonVEI");

        $c1 = $c2 = $this->getMon2();
        foreach ($d1 as $k => $v) {
            if (!isset($c1[$v->oaWonVEI])) {
                continue;
            }
            $c1[$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_1 = [
            'name' => 'year_2020',
            'type' => 'bar',
            // 'barWidth' => '20%',
            'stack' =>  'total',
            'color' => '#1891ff',
            'data' => array_values($c1),
        ];
        foreach ($d2 as $k => $v) {
            if (!isset($c2[$v->oaWonVEI])) {
                continue;
            }
            $c2[$v->oaWonVEI] = $v->tX1xYm5Q;
        }
        $c1_2 = [
            'name' => 'year_2019',
            'type' => 'bar',
            // 'barWidth' => '20%',
            'stack' =>  'total',
            'color' => '#2fc25b',
            'data' => array_values($c2),
        ];

        $data = [
            'series' => [
                $c1_1,
                $c1_2
            ],
            'title' => ts('HBT'),
            'legend' => ['year_2020', 'year_2019'],
            'xAxis' => $this->getMon1(),
        ];
        return $data;
    }


    // 1-12月本地化
    protected function getMon1()
    {
        $c1_xAxis = [];
        foreach (range(1,12) as $v) {
            $c1_xAxis[] = ts('MON'.$v);
        }
        return $c1_xAxis;
    }
    // 补零后的1-12
    protected function getMon2()
    {
        $tm = [];
        foreach (range(1,12) as $v) {
            $k = str_pad($v, 2, 0, STR_PAD_LEFT);
            $tm[$k] = 0;
        }
        return $tm;
    }


}
